package cn.edu.seu.herald.informationplatform.dao.database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.mysql.jdbc.Statement;

import cn.edu.seu.herald.informationplatform.bean.league.LeagueInfo;
import cn.edu.seu.herald.informationplatform.bean.league.Picture;


public class PictureDao extends DataAccessObject{
	
	private static PictureDao daoInstance=null;
	protected PictureDao(){
	}
	public static PictureDao getDao(){
		if(daoInstance==null){
			daoInstance=new PictureDao();
		}
		return daoInstance;
	}
	
	//add
	public int addPicture(Picture newPicture) throws SQLException{
		Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("insert into lg_picture(league_id,name,address,album_id) values(?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
    	ps.setInt(1,newPicture.getLeagueId());
    	ps.setString(2,newPicture.getName());
    	ps.setString(3,newPicture.getAddress());
    	ps.setInt(4,newPicture.getAlbumId());
    	ps.executeUpdate();
    	ResultSet rsResultSet=ps.getGeneratedKeys();
		int new_id=-1;
		if(rsResultSet.next()){
			new_id=rsResultSet.getInt(1);
		}
		closeRs(rsResultSet);
		return new_id;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	//delete
	public void deletePicture(int id) throws SQLException{
		Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("delete from lg_picture where id=?");
    	ps.setInt(1,id);
    	ps.executeUpdate();
    	}catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	//delete
	public void deletePicturesByAlbumId(int albumId) throws SQLException{
		Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("delete from lg_picture where album_id=?");
    	ps.setInt(1,albumId);
    	ps.executeUpdate();
    	}catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	//update
	
	//getPicturesByAlbumId
	//Return:Map<id,pictureObject>
    public Map<Integer,Picture> getPicturesByAlbumId(int AlbumId) throws SQLException{
    	Connection con=getConnectionLeague();
    	HashMap<Integer,Picture> idPictureMap=new HashMap<Integer, Picture>();
    	try{
    	PreparedStatement ps=con.prepareStatement("select * from lg_picture where album_id=? ");
    	ps.setInt(1,AlbumId);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Picture picture=new Picture();
    		picture.setAddress(rsResultSet.getString(4));
    		picture.setName(rsResultSet.getString(3));
    		picture.setLeagueId(rsResultSet.getInt(2));
    		idPictureMap.put(rsResultSet.getInt(1),picture);
    	}
    	closeRs(rsResultSet);
    	return idPictureMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
        }
    	finally{
			closeCon(con);
		}
    }
    
    //getCoverAddressByAlbumId
    public String getCoverAddressByAlbumId(int AlbumId) throws SQLException{
    	Connection con=getConnectionLeague();
    	String coverAddress = null;
    	try{
    	PreparedStatement ps=con.prepareStatement("select address from lg_picture where album_id=? ");
    	ps.setInt(1,AlbumId);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		return rsResultSet.getString(1);
    	}
    	closeRs(rsResultSet);
    	return coverAddress;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
        }
    	finally{
			closeCon(con);
		}
    }
    
  //getPicture
    public Picture getPictureById(int PictureId) throws SQLException{
    	Connection con=getConnectionLeague();
    	try{
    	PreparedStatement ps=con.prepareStatement("select * from lg_picture where id=? ");
    	ps.setInt(1,PictureId);
    	ResultSet rsResultSet=ps.executeQuery();
    	Picture picture=new Picture();
    	while(rsResultSet.next()){
    		picture.setAddress(rsResultSet.getString(4));
    		picture.setAlbumId(rsResultSet.getInt(5));
    		picture.setName(rsResultSet.getString(3));
    		picture.setLeagueId(rsResultSet.getInt(2));
    	}
    	closeRs(rsResultSet);
    	return picture;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
  //update
	public void updatePictureName(int id,String newName) throws SQLException{
		Connection con=getConnectionLeague();
		try {
				PreparedStatement ps=con.prepareStatement("update lg_picture set name=? where id=?");
				ps.setString(1,newName);
				ps.setInt(2,id);
				ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
}
